package com.chris.webservice.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * JDBC查询基类，基于JDBC的查询继承本类 注意修改数据源的mappedName值
 * 
 * @author ranhualin
 * @version $$Revision: 13462 $$
 */
@SuppressWarnings({"unchecked","rawtypes"})
public abstract class BaseJdbcDao {
	protected static final Logger logger = LoggerFactory.getLogger(BaseJdbcDao.class);

	private DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	/**
	 * 子类必须实现的抽象方法，用于回调获得由子类注入的DataSource
	 * 
	 * @return
	 */
	protected abstract DataSource callbackDataSource();

	/**
	 * 检查DataSource是否为空，是否需要回调获取DataSource
	 */
	protected void checkDataSource() {
		if (dataSource == null)
			dataSource = callbackDataSource();

		if (dataSource == null);
//			throw new SystemException(ErrorCodeConstants.NULL_ENTITY_ERROR);
	}

	public Connection getConnection() throws SQLException {
		checkDataSource();
		return dataSource.getConnection();
	}

	public Statement getStatement(Connection connection) throws SQLException {
		return connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
	}

	public void closeStatement(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {
			}
		}
	}

	public void closeConnection(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * 
	 * 以JDBC的方式分页查询(需要数据库支持absolute)，返回封装结果<字段名，值>的Map的集合
	 * 
	 * @param sql
	 *            sql语句
	 * @param first
	 *            开始行号
	 * @param pageSize
	 *            每页记录数
	 * @return
	 * @throws java.sql.SQLException
	 */
	public List<Map<String, Object>> findPageMaps(String sql, int first,
			int pageSize) throws SQLException {

		Connection connection = getConnection();
		Statement statement = getStatement(connection);
		List list = new ArrayList();
		int count = 1;
		Map<String, Object> valueMap;
		ResultSet rs = statement.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columns = rsmd.getColumnCount();
		if (rs.absolute(first)) {
			while (rs.next()) {
				valueMap = new HashMap<String, Object>();
				for (int i = 1; i <= columns; i++) {
					valueMap.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				count++;
				list.add(valueMap);
				if (count > pageSize) {
					break;
				}
			}
		}
		closeStatement(statement);
		closeConnection(connection);
		return list;
	}

	/**
	 *
	 * 以JDBC的方式分页查询(需要数据库支持absolute)，返回封装结果<值>的数组
	 *
	 * @param sql
	 *            sql语句
	 * @param first
	 *            开始行号
	 * @param pageSize
	 *            每页记录数
	 * @return
	 * @throws java.sql.SQLException
	 */
	public List<Object[]> findPageArray(String sql, int first, int pageSize)
			throws SQLException {
		Connection connection = getConnection();
		Statement statement = getStatement(connection);
		List list = new ArrayList();
		int count = 1;
		Object[] values;
		ResultSet rs = statement.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columns = rsmd.getColumnCount();
		if (rs.absolute(first)) {
			while (rs.next()) {
				values = new Object[columns];
				for (int i = 1; i <= columns; i++) {
					values[i - 1] = rs.getObject(i);
				}
				list.add(values);
				count++;
				if (count > pageSize) {
					break;
				}
			}
		}
		closeStatement(statement);
		closeConnection(connection);
		return list;
	}

	/**
	 * 查询数据
	 *
	 * @param sql
	 * @return
	 * @throws java.sql.SQLException
	 */
	public List<Object[]> findArray(String sql) throws SQLException {
		Connection connection = getConnection();
		Statement statement = getStatement(connection);
		List list = new ArrayList();
		Object[] values;
		ResultSet rs = statement.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columns = rsmd.getColumnCount();

		while (rs.next()) {
			values = new Object[columns];
			for (int i = 1; i <= columns; i++) {
				values[i - 1] = rs.getObject(i);
			}
			list.add(values);
		}
		closeStatement(statement);
		closeConnection(connection);
		return list;
	}

	/**
	 * 执行批量的更新操作
	 * @param sqls
	 * @return
	 */
	public int[] executeBatch(String[] sqls) {
		Connection connection = null;
		Statement statement = null;
		int[] rs = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			for (String sql : sqls) {
				statement.addBatch(sql);
			}
			rs = statement.executeBatch();
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			closeStatement(statement);
			closeConnection(connection);
		}
		return rs;
	}
	/**
	 * 使用jdbc执行更细操作
	 * @param sql
	 * @return
	 */
	public boolean execute(String sql) {
		Connection connection = null;
		Statement statement = null;
		boolean rs = false;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			rs = statement.execute(sql);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			closeStatement(statement);
			closeConnection(connection);
		}
		return rs;
	}
}
